const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

function createPBSTables() {
  const db = new Database(DB_PATH);

  try {
    // 创建PBS层级结构表
    const createPBSTable = `
      CREATE TABLE IF NOT EXISTS pbs_levels (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        level1_area TEXT NOT NULL,
        level2_analysis TEXT,
        level3_subitem TEXT NOT NULL,
        full_path TEXT NOT NULL,
        pbs_code TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    // 创建实例PBS映射表
    const createMappingTable = `
      CREATE TABLE IF NOT EXISTS instance_pbs_mapping (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        instance_id TEXT NOT NULL,
        pbs_level_id INTEGER NOT NULL,
        pbs_instance_id TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (pbs_level_id) REFERENCES pbs_levels(id),
        UNIQUE(instance_id, pbs_level_id)
      )
    `;

    // 创建索引
    const createIndexes = [
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_area ON pbs_levels(level1_area)',
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_analysis ON pbs_levels(level2_analysis)',
      'CREATE INDEX IF NOT EXISTS idx_pbs_levels_code ON pbs_levels(pbs_code)',
      'CREATE INDEX IF NOT EXISTS idx_instance_pbs_instance ON instance_pbs_mapping(instance_id)',
      'CREATE INDEX IF NOT EXISTS idx_instance_pbs_level ON instance_pbs_mapping(pbs_level_id)'
    ];

    console.log('创建PBS层级结构表...');
    db.exec(createPBSTable);
    
    console.log('创建实例PBS映射表...');
    db.exec(createMappingTable);
    
    console.log('创建索引...');
    createIndexes.forEach(indexSQL => {
      db.exec(indexSQL);
    });

    console.log('PBS表结构创建完成！');
  } catch (error) {
    console.error('创建PBS表结构失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

function generatePBSCode(level1, level2, level3) {
  // 生成PBS编码：区域-分析-子分项
  const area = level1.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4);
  const analysis = level2 ? level2.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 4) : '';
  const subitem = level3.replace(/[^\u4e00-\u9fa5\w]/g, '').substring(0, 6);
  
  return analysis ? `${area}-${analysis}-${subitem}` : `${area}-${subitem}`;
}

function importPBSData() {
  const csvPath = path.join(process.cwd(), 'excel', 'PBS层级结构-0709.csv');
  
  if (!fs.existsSync(csvPath)) {
    throw new Error(`CSV文件不存在: ${csvPath}`);
  }

  const csvContent = fs.readFileSync(csvPath, 'utf-8');
  const lines = csvContent.split('\n').filter(line => line.trim());
  
  const db = new Database(DB_PATH);
  
  try {
    const insertStmt = db.prepare(`
      INSERT OR REPLACE INTO pbs_levels 
      (level1_area, level2_analysis, level3_subitem, full_path, pbs_code)
      VALUES (?, ?, ?, ?, ?)
    `);

    let currentArea = '';
    let currentAnalysis = '';
    let imported = 0;
    let skipped = 0;

    console.log('开始导入PBS数据...');

    for (const line of lines) {
      // 跳过表头
      if (line.includes('区域,分析,子分项')) continue;

      // 解析CSV行 - 处理中文逗号分隔
      const cleanLine = line.replace(/^\d+→/, ''); // 移除行号标记
      const columns = cleanLine.split(',').map(col => col.trim().replace(/^"|"$/g, ''));
      
      if (columns.length < 3) {
        skipped++;
        continue;
      }

      const [area, analysis, subitem] = columns;

      // 更新当前层级
      if (area && area !== '') {
        currentArea = area;
      }
      if (analysis && analysis !== '') {
        currentAnalysis = analysis;
      }

      // 必须有子分项
      if (!subitem || subitem === '' || subitem === '-') {
        skipped++;
        continue;
      }

      const fullPath = currentAnalysis ? 
        `${currentArea} > ${currentAnalysis} > ${subitem}` : 
        `${currentArea} > ${subitem}`;
      
      const pbsCode = generatePBSCode(currentArea, currentAnalysis, subitem);

      try {
        insertStmt.run(currentArea, currentAnalysis || null, subitem, fullPath, pbsCode);
        imported++;
        console.log(`导入: ${fullPath} [${pbsCode}]`);
      } catch (error) {
        console.warn(`跳过重复项: ${pbsCode} - ${error.message}`);
        skipped++;
      }
    }

    console.log(`\nPBS数据导入完成！导入: ${imported} 条，跳过: ${skipped} 条`);
    
    // 显示导入结果统计
    const countStmt = db.prepare('SELECT COUNT(*) as total FROM pbs_levels');
    const total = countStmt.get();
    console.log(`数据库中共有 ${total.total} 条PBS层级记录`);

    // 显示前10条记录作为验证
    console.log('\n前10条PBS记录：');
    const sampleStmt = db.prepare('SELECT * FROM pbs_levels LIMIT 10');
    const samples = sampleStmt.all();
    samples.forEach(record => {
      console.log(`- ${record.full_path} [${record.pbs_code}]`);
    });

  } catch (error) {
    console.error('导入PBS数据失败:', error);
    throw error;
  } finally {
    db.close();
  }
}

// 生成实例PBS层级ID的函数
function generateInstancePBSId(instanceId, pbsLevelId) {
  const timestamp = Date.now();
  const random = Math.random().toString(36).substring(2, 8);
  return `${instanceId}-PBS-${pbsLevelId}-${timestamp}-${random}`;
}

// 为实例分配PBS层级的函数
function assignPBSToInstance(instanceId, pbsLevelId) {
  const db = new Database(DB_PATH);
  
  try {
    // 检查是否已经分配过
    const existingStmt = db.prepare(`
      SELECT pbs_instance_id FROM instance_pbs_mapping 
      WHERE instance_id = ? AND pbs_level_id = ?
    `);
    const existing = existingStmt.get(instanceId, pbsLevelId);
    
    if (existing) {
      return existing.pbs_instance_id;
    }

    // 生成新的PBS实例ID
    const pbsInstanceId = generateInstancePBSId(instanceId, pbsLevelId);
    
    const insertStmt = db.prepare(`
      INSERT INTO instance_pbs_mapping (instance_id, pbs_level_id, pbs_instance_id)
      VALUES (?, ?, ?)
    `);
    
    insertStmt.run(instanceId, pbsLevelId, pbsInstanceId);
    
    return pbsInstanceId;
  } finally {
    db.close();
  }
}

// 获取所有PBS层级的函数
function getAllPBSLevels() {
  const db = new Database(DB_PATH);
  
  try {
    const stmt = db.prepare(`
      SELECT * FROM pbs_levels 
      ORDER BY level1_area, level2_analysis, level3_subitem
    `);
    return stmt.all();
  } finally {
    db.close();
  }
}

// 获取实例的PBS映射
function getInstancePBSMapping(instanceId) {
  const db = new Database(DB_PATH);
  
  try {
    const stmt = db.prepare(`
      SELECT 
        ipm.*,
        pl.level1_area,
        pl.level2_analysis,
        pl.level3_subitem,
        pl.full_path,
        pl.pbs_code
      FROM instance_pbs_mapping ipm
      JOIN pbs_levels pl ON ipm.pbs_level_id = pl.id
      WHERE ipm.instance_id = ?
      ORDER BY ipm.created_at
    `);
    return stmt.all(instanceId);
  } finally {
    db.close();
  }
}

// 主执行函数
async function main() {
  try {
    console.log('开始创建PBS层级结构系统...\n');
    
    // 1. 创建表结构
    createPBSTables();
    
    // 2. 导入数据
    importPBSData();
    
    console.log('\n✅ PBS层级结构系统创建完成！');
    console.log('\n📖 使用方法：');
    console.log('1. 在实例中选择PBS层级时，调用 getAllPBSLevels() 获取所有层级');
    console.log('2. 选择层级后，调用 assignPBSToInstance(instanceId, pbsLevelId) 生成唯一ID');
    console.log('3. 使用 getInstancePBSMapping(instanceId) 查看实例的PBS映射');
    
  } catch (error) {
    console.error('❌ 执行失败:', error);
    process.exit(1);
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}

module.exports = {
  generateInstancePBSId,
  assignPBSToInstance,
  getAllPBSLevels,
  getInstancePBSMapping,
  createPBSTables,
  importPBSData
};